using System;
using System.Data;
using System.Text;
using BrightFrame;
using AIM.Model;
using System.Collections.Generic;
namespace AIM.DAL
{
	public class CustodyPeriodDAL
	{
        private DbProvider dbProvider = new DbProvider();

        public void Add(CustodyPeriod custodyPeriod)
        {
            dbProvider.ExecuteNonQuery("INSERT INTO custody_period (CustodyPeriodName) VALUES (?CustodyPeriodName)",
                dbProvider.CreateParameter("?CustodyPeriodName", DbType.AnsiString, custodyPeriod.CustodyPeriodName));
        }

        public void Update(CustodyPeriod custodyPeriod)
        {
            dbProvider.ExecuteNonQuery(@"
UPDATE custody_period
SET CustodyPeriodName=?CustodyPeriodName
WHERE CustodyPeriodID=?CustodyPeriodID",
                dbProvider.CreateParameter("?CustodyPeriodID", DbType.Int32, custodyPeriod.CustodyPeriodID),
                dbProvider.CreateParameter("?CustodyPeriodName", DbType.AnsiString, custodyPeriod.CustodyPeriodName));
        }

        public void Delete(int custodyPeriodID)
        {
            dbProvider.ExecuteNonQuery(@"
DELETE FROM custody_period
WHERE CustodyPeriodID=?CustodyPeriodID
",
                dbProvider.CreateParameter("?CustodyPeriodID", DbType.Int32, custodyPeriodID));
        }

        public List<CustodyPeriod> QueryAll()
        {
            List<CustodyPeriod> custodyPeriodList = new List<CustodyPeriod>();

            using (IDataReader dr = dbProvider.ExecuteDataReader(@"
SELECT 
    CustodyPeriodID,
    CustodyPeriodName 
FROM custody_period"))
            {
                while (dr.Read())
                {
                    custodyPeriodList.Add(new CustodyPeriod
                    {
                        CustodyPeriodID = Convert.ToInt32(dr["CustodyPeriodID"]),
                        CustodyPeriodName = dr["CustodyPeriodName"].ToString()
                    });
                }
            }

            return custodyPeriodList;
        }

        public CustodyPeriod GetModel(int custodyPeriodID)
        {
            CustodyPeriod custodyPeriod = null;
            using (IDataReader dr = dbProvider.ExecuteDataReader(@"
SELECT 
    CustodyPeriodID,
    CustodyPeriodName 
FROM custody_period
WHERE CustodyPeriodID=?CustodyPeriodID",
                dbProvider.CreateParameter("?CustodyPeriodID", DbType.Int32, custodyPeriodID)))
            {
                if (dr.Read())
                {
                    custodyPeriod = new CustodyPeriod
                    {
                        CustodyPeriodID = Convert.ToInt32(dr["CustodyPeriodID"]),
                        CustodyPeriodName = dr["CustodyPeriodName"].ToString()
                    };
                }
            }
            return custodyPeriod;
        }

        public int CountByCustodyPeriodID(int custodyPeriodID)
        {
            return Convert.ToInt32(dbProvider.ExecuteScalar(@"
SELECT 
    (SELECT COUNT(*) FROM archive_f WHERE CustodyPeriodID=?CustodyPeriodID)+
    (SELECT COUNT(*) FROM archive_v WHERE CustodyPeriodID=?CustodyPeriodID)
", 
                dbProvider.CreateParameter("?CustodyPeriodID", DbType.Int32, custodyPeriodID)));
        }

        public CustodyPeriod GetByCustodyPeriodName(string custodyPeriodName)
        {
            CustodyPeriod custodyPeriod = null;
            using (IDataReader dr = dbProvider.ExecuteDataReader(@"
SELECT 
    CustodyPeriodID,
    CustodyPeriodName 
FROM custody_period
WHERE CustodyPeriodName=?CustodyPeriodName",
                dbProvider.CreateParameter("?CustodyPeriodName", DbType.AnsiString, custodyPeriodName)))
            {
                if (dr.Read())
                {
                    custodyPeriod = new CustodyPeriod
                    {
                        CustodyPeriodID = Convert.ToInt32(dr["CustodyPeriodID"]),
                        CustodyPeriodName = dr["CustodyPeriodName"].ToString()
                    };
                }
            }
            return custodyPeriod;
        }
	}
}

